package com.webservice.dao;


import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.dom4j.Element;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.webservice.utils.UUIDUtil;
import com.webservice.vo.IBaseVO;


/**
 * <b> 公共数据库持久层类 </b>
 * <p>
 * 功能:所有对数据库的操作均通过此入口类操作。
 * </p>
 * 
 * @作者 Andy
 * @创建日期 2012-5-16
 * @项目名称 estore
 * @JAVA路径 com.webservice.dao.PubBaseDao
 */
@Repository("pubBaseDao")
public class PubBaseDao {
	@Autowired
	@Qualifier("datatemplate")
	private JdbcTemplate jdbcTemplate;
	
	private static Logger logger = Logger.getLogger(PubBaseDao.class);
//	public static Hashtable<Thread,Object> htThreadConn = new Hashtable<Thread,Object>();
//	public static ConnManageThread connManageThread = null;
	public PubBaseDao() {
	}
	
	
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}



	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

//	public BigDecimal test(){//rowMapper
//		BigDecimal a=(BigDecimal) this.jdbcTemplate.query("select 1 from dual",new RowMapper<BigDecimal>() {
//			public BigDecimal mapRow(ResultSet rs, int arg1) throws SQLException {
//				BigDecimal a=rs.getBigDecimal(1);
//				logger.debug(a);
//				return a;
//			}
//		});
//		return a;
//	}


	/***
	 * 
	 * @return
	 * @throws Exception
	 */
//	protected static Connection getDBConn() throws Exception {
//		Params param=Params.getInstance();		
//		Class.forName("oracle.jdbc.driver.OracleDriver");
////		System.out.println("用户名："+param.getDb_user());
////		System.out.println("密码："+param.getDb_pass());
////		System.out.println("url："+param.getDb_url());
//		Connection conn = DriverManager.getConnection(param.getDb_url(),param.getDb_user(),param.getDb_pass());
//		return conn;
//	}
		
//	public static Connection getConnection() throws Exception {
//		Object objConn = htThreadConn.get(Thread.currentThread());
//		Connection conn = null;
//		
//		if(connManageThread==null) {
//			connManageThread = new ConnManageThread();
//			connManageThread.start();
//		}
//		
//		if(objConn==null || ((Connection)objConn).isClosed()) {
//			objConn = getDBConn();
//			htThreadConn.put(Thread.currentThread(), objConn);
//		} 
//		
//		conn = (Connection)objConn;
//		return conn;
//	}
	
	/**
	 * 放回最小值：vo/pageCount
	 * @param c
	 * @param whereSQL
	 * @param pageCount
	 * @return
	 * @throws Exception
	 */
//	public int getTotalPage(Class<?> c,String whereSQL,double pageCount)throws Exception{
//		List<BaseVO> vos= queryByWhere(c, whereSQL);
//		int totalPage=(int)Math.ceil(vos.length/pageCount);
//		return totalPage;
//	}
			
	public List<IBaseVO> queryAll(Class<?> c) throws Exception {
		return queryByWhere(c, null);
	}
	
//	public ValueObject[] queryAllForPage(Class c,int page,int pageCount) throws Exception {
//		return queryByWhere(c, " 1=1 limit "+(page-1)*pageCount+","+pageCount);
//	}
//	
//	public ValueObject[] queryByWhereForPage(Class c,String whereSQL,int page,int pageCount) throws Exception {
//		return queryByWhere(c, whereSQL+" limit "+(page-1)*pageCount+","+pageCount);
//	}
	/***
	 * 通过条件查询
	 * @param c
	 * @param whereSQL
	 * @return
	 * @throws Exception
	 */
	public List<IBaseVO> queryByWhere(final Class<?> c,String whereSQL) throws Exception {
	    final IBaseVO vo = (IBaseVO)c.newInstance();
		String sql = "select ";
		sql+=Pub.getAllFields( vo,false);
		sql+=" from "+vo.getTableName();
		if(whereSQL!=null) {
			sql+="  where "+whereSQL;
		}
//		return jdbcTemplate.query(sql, new RowMapper<IBaseVO>(){
//			public IBaseVO mapRow(ResultSet rs, int arg1) throws SQLException {
//				IBaseVO basevo=null;
//				try {
//					basevo=Pub.setBaseValue(vo,rs);
//				} catch (Exception e) {
//					e.printStackTrace();
//				}
//				return basevo;
//			}
//		});
		return query(sql,  c);
	}
	
	/***
	 * 
	 * @param sql
	 * @param clazz
	 * @return
	 * @throws Exception
	 */
	public List<IBaseVO> query(String sql,Class<?> c)throws Exception{
		final IBaseVO vo = (IBaseVO)c.newInstance();
		return jdbcTemplate.query(sql, new RowMapper<IBaseVO>(){
			public IBaseVO mapRow(ResultSet rs, int arg1) throws SQLException {
				IBaseVO basevo=null;
				try {
					basevo=Pub.setBaseValue(vo,rs);
				} catch (Exception e) {
					e.printStackTrace();
				}
				return basevo;
			}
		});
	}
	
	/***
	 * 
	 * @param sql
	 * @param parms
	 * @param c
	 * @return
	 * @throws Exception
	 */
	public List<IBaseVO> query(String sql,Object[] parms,Class<?> c)throws Exception{
		final IBaseVO vo = (IBaseVO)c.newInstance();
		return jdbcTemplate.query(sql, parms, new RowMapper<IBaseVO>(){
			@Override
			public IBaseVO mapRow(ResultSet rs, int arg1) throws SQLException {
				IBaseVO basevo=null;
				try {
					basevo=Pub.setBaseValue(vo,rs);
				} catch (Exception e) {
					e.printStackTrace();
				}
				return basevo;
			}
			
		});
	}
	
//	public BaseVO[] queryByWhere(Class c,String whereSQL) throws Exception {
//		
//		String sql = "select ";
//		BaseVO vo = (BaseVO)c.newInstance();
//		String [] fieldNames = vo.getFieldNames();
////		String [] fieldTypes = vo.getFieldTypeNames();
//		for (int i = 0; i < fieldNames.length; i++) {
//			sql+=fieldNames[i];
//			if(i<fieldNames.length-1) {
//				sql+=",";
//			}
//		}
//		sql+=" from "+vo.getTableName();
//		if(whereSQL!=null) {
//			sql+=" as c where "+whereSQL;
//		}
//		
////		System.out.println("SQL["+Pub.getCurrTime()+"] "+sql);
//		
//		Connection conn = null;
//		PreparedStatement stmt = null;
//		BaseVO [] vos = null;
//		try {
//			conn = getConnection();
//			stmt = conn.prepareStatement(sql);
//			ResultSet rs = stmt.executeQuery();
//			Vector v = new Vector();
//			while(rs.next()) {
//				BaseVO vo1 = (BaseVO)c.newInstance();
//				
//				for (int i = 0; i < fieldNames.length; i++) {
//					if(fieldTypes[i].equalsIgnoreCase("INT")){
//						Method m = c.getMethod("set"+fieldNames[i], new Class[]{int.class});
//						m.invoke(vo1, new Object[]{new Integer(rs.getInt(i+1))});
//					} else {
//						Method m = c.getMethod("set"+fieldNames[i], new Class[]{String.class});
//						m.invoke(vo1, new Object[]{rs.getString(i+1)});
//					}
//				}
//				
//				v.add(vo1);
//			}
//			vos = new BaseVO[v.size()];
//			v.copyInto(vos);
//		} finally {
//			try { if(stmt!=null) stmt.close(); } catch (Exception e) {}
//			//try { if(conn!=null) conn.close(); } catch (Exception e) {}
//		}
//		return vos;
//	}
	
//	public synchronized static String getID() throws Exception {
//		return UUIDUtills.getUUID();
//	}
	
	public void update(IBaseVO vo) throws Exception {
		List<IBaseVO> list=new ArrayList<IBaseVO>();
		list.add(vo);
		update(list);		
	}
	
	public int update(String sql){
		String[] sqls=new String []{sql};
		return jdbcTemplate.batchUpdate(sqls)[0];
	}
	
	public int[] update(String[] sql){
		return jdbcTemplate.batchUpdate(sql);
	}
	
	public void update(final List<IBaseVO> vos) throws Exception {
		if(vos==null || vos.size()==0) return;
		
		IBaseVO vo = vos.get(0);
		if(vo.getPrimaryKey()==null)  throw new Exception("表没有主键字段，无法进行更新");
		String sql = "update "+vo.getTableName()+" set ";
		sql+=Pub.getAllFields( vo, true);
		sql+=" where "+vo.getPrimaryKey()+"=?";
		sql+="";
		
		System.out.println("SQL["+Pub.getCurrTime()+"] "+sql);
		
//		Connection conn = null;
//		PreparedStatement stmt = null;
//		try {
//			conn = getConnection();
//			stmt = conn.prepareStatement(sql);
//			logger.debug("SQL["+Pub.getCurrTime()+"] "+sql);
//			for (int i = 0; i < vos.length; i++) {
//				Pub.updateBaseValue(vo, stmt);
//				Method m = vos[i].getClass().getMethod("get"+Pub.firstLetterToUpper(vos[i].getPrimaryKey()), new Class[0]);
//				Object obj = m.invoke(vos[i], new Object[0]);
//				stmt.setString(Pub.getVOFieldType(vo).size()+1, obj==null?null:obj.toString());
//				stmt.addBatch();
//			}
//			
//			stmt.executeBatch();
//			
//		} finally {
//			try { if(stmt!=null) stmt.close(); } catch (Exception e) {}
//		}
//		BatchPreparedStatementSetter batchPreparedStatementSetter = new BatchPreparedStatementSetter(){
//
//			@Override
//			public int getBatchSize() {
//				return 0;
//			}
//
//			public void setValues(PreparedStatement arg0, int arg1)throws SQLException {
//				arg0.
//			}
//			
//		};
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){
			public int getBatchSize() {
				return vos.size();
			}
			@Override
			public void setValues(PreparedStatement statement, int arg1)throws SQLException {
				for(IBaseVO vo: vos){
					try {
						Pub.updateBaseValue(vo, statement);
						Method m = vo.getClass().getMethod("get"+Pub.firstLetterToUpper(vo.getPrimaryKey()), new Class[0]);
						Object obj = m.invoke(vo, new Object[0]);
						statement.setString(Pub.getVOFieldType(vo).size()+1, obj==null?null:obj.toString());
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		});
	}
	
//	public void updateBySQL(String sql) throws Exception {
//		Connection conn = null;
//		PreparedStatement stmt = null;
//		try {
//			conn = getConnection();
//			stmt = conn.prepareStatement(sql);
//			stmt.executeUpdate();
//		} finally {
//			try { if(stmt!=null) stmt.close(); } catch (Exception e) {}
//		}
//	}
	
	public int insert(IBaseVO vo) {
		List<IBaseVO> list=new ArrayList<IBaseVO>();
		list.add(vo);
		return insert(list)[0];
	}
	
//	public Object [][] querySQL(String sql) throws Exception {
//		Object [][] data = null;
//		Connection conn = null;
//		PreparedStatement stmt = null;
//		
//		try {
//			conn = getConnection();
//			stmt = conn.prepareStatement(sql);
//			ResultSet rs = stmt.executeQuery();
//			Vector<Object []> v = new Vector<Object []>();
//			ResultSetMetaData md = rs.getMetaData();
//			while(rs.next()) {
//				Object [] row = new Object[md.getColumnCount()];
//				for (int i = 0; i < row.length; i++) {
//					row[i] = rs.getObject(i+1);
//				}
//				v.add(row);
//			}
//			data = new Object[v.size()][];
//			v.copyInto(data);
//		} finally {
//			try { if(stmt!=null) stmt.close(); } catch (Exception e) {}
//		}
//		return data;
//	}
	
	/***
	 * 公共的插入方法
	 * @param vos
	 * @return
	 * @throws Exception
	 */
	public  int[] insert(final List<IBaseVO> vos){
		if(vos==null || vos.size()==0) return new int[0];
		IBaseVO vo = vos.get(0);
		String sql = getSql(vo);
		final String [] keys = new String[vos.size()];
		return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){
			@Override
			public int getBatchSize() {
				return vos.size();
			}
			@Override
			public void setValues(PreparedStatement stmt, int arg1)throws SQLException {
				for(int i=0;i<vos.size();i++){
					logger.debug("开始插入数据___________________");
					try {
						updateBaseValues(vos.get(i), stmt, keys, i,null,null);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		});
	}


	/***
	 * 得到查询的sql
	 * @param vo
	 * @return
	 */
	private String getSql(IBaseVO vo) {
		String sql = "insert into "+vo.getTableName()+" (";
		System.out.println(Pub.getAllFields( vo, false));
		sql+=Pub.getAllFields(vo, false);
		sql+=") values (";
		sql+=Pub.getAllFieldValues(vo, true);
		sql+=")";
		logger.debug("执行sql："+sql);
		return sql;
	}
	
	
	/***
	 * 
	 * @param vo
	 * @param stmt
	 * @param keys
	 * @param a
	 * @throws Exception
	 */
	public  void updateBaseValues(IBaseVO vo,PreparedStatement stmt,String [] keys,int a,String foreignKey,String foreignValue)throws Exception{
		Map<String,String> fieldMap=Pub.getVOFieldType(vo);
		int i=0;
		for (String key : fieldMap.keySet()) {
			/**外键赋值**/
			if(null!=foreignKey&&null!=foreignValue){
				Method md = vo.getClass().getMethod("set"+foreignKey, new Class[]{String.class});
				md.invoke(vo, new Object[]{foreignValue});
			}
			
			Method m = vo.getClass().getMethod("get"+key, new Class[0]);
			Object obj = m.invoke(vo, new Object[0]);
			logger.debug("___________字段数据："+obj);
			String primaryValue= UUIDUtil.getUUID();
			if(vo.getPrimaryKey()!=null&& vo.getPrimaryKey().equalsIgnoreCase(key)) {
				if(obj==null){//||Integer.parseInt(obj.toString())==0
					keys[a] =primaryValue;//getID();
					stmt.setString(i+1, keys[a]);
					logger.debug("___________主键："+primaryValue);
				}else{
					stmt.setString(i+1, (String)obj);
				}
				Method m1 = vo.getClass().getMethod("set"+key, new Class[]{String.class});
				m1.invoke(vo, new Object[]{primaryValue});
			}else {
				if(fieldMap.get(key).equalsIgnoreCase("BigDecimal")||fieldMap.get(key).equalsIgnoreCase("INT")) {
					stmt.setBigDecimal(i+1,new BigDecimal(obj.toString()));
				}else if(fieldMap.get(key).equalsIgnoreCase("List")){//如果有list类型
					 @SuppressWarnings("unchecked")
					List<IBaseVO>  taskList = (ArrayList<IBaseVO>)obj;
					 if(taskList!=null&&taskList.size()>0){
						 insert(taskList, vo.getPrimaryKey(), primaryValue);
					 }
				} 
				else {
					stmt.setString(i+1, (String)obj);
				}
			}
			i++;
		}
	}
	
	/***
	 * 公共的插入方法
	 * @param vos
	 * @return
	 * @throws Exception
	 */
	public  int[] insert(final List<IBaseVO> vos,final String primaryKey,final String primaryValue) throws Exception {
		if(vos==null || vos.size()==0) return new int[0];
		IBaseVO vo = vos.get(0);
		String sql = getSql(vo);
		final String [] keys = new String[vos.size()];
		return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){
			@Override
			public int getBatchSize() {
				return vos.size();
			}
			@Override
			public void setValues(PreparedStatement stmt, int arg1)throws SQLException {
				for(int i=0;i<vos.size();i++){
					logger.debug("开始插入数据___________________");
					try {
						updateBaseValues(vos.get(i), stmt, keys, i, primaryKey, primaryValue);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		});
	}
	
	/**
	 * 
	 * @param sql 查询的sql 只有一个字段
	 * @param parms 参数
	 * @param name 返回的字段
	 * @return
	 */
	public List<String> getList(String sql, Object[] parms,final String name) {
		List<String> list=jdbcTemplate.query(sql, parms, new RowMapper<String>(){
			@Override
			public String mapRow(ResultSet rs, int i)throws SQLException {
				return rs.getString(name);
			}
		});
		return list;
	}
}
